<?php
namespace control;
/*
*考勤统计类
*/

use JetBrains\PhpStorm\NoReturn;

class clockStatisticsClass extends adIndexClass
{
    private const page = 'clockStatistics';
    private const table = 'work_sign';
    private const title = '考勤记录';

    /**
     * 查询表单
     *
     * @param string $workFlow
     * @return string
     */
    public function homeForm(string $workFlow): string
    {
        //生成查询表单
        if ($workFlow == '考勤明细') {
            $itherHtml = text(array("name" => "createTime", 'class'=>'width200','laydate' => ['type' => 'date', 'range' => '~'], "title" => "日期"));
        } else {
            $itherHtml = text(array("name" => "month", 'class'=>'text','laydate' => ['type' => 'month'], "title" => "月份"));
        }
        $html =
            selectMultiple(array("name" => "stid[]", "title" => "员工",'multiple'=>true, "table" => "staff",  "where" => " state <> '离职' ", 'class' => 'text', 'sqlField' => 'name', 'sqlId' => 'stid')).
            $itherHtml;

        return $this->search($html, 'menu');
    }

    /*列表-查询条件*/
    public function home($workFlow = '考勤明细'): string
    {
        $data = [
            "id" => "all",
            "menu" => [
                "details" => "考勤明细",
                "collect" => "考勤汇总",
            ]
        ];
        $data['html'] = $this->homeForm($workFlow);
        return $this->tab($data);
    }

    /*客户列表-查询结果*/
    public function homeSearch(): bool|string
    {
        $and = "1=1";
        if ($this->post('stid')) {
            $and .= " and stid in ('".implode("','",$this->post('stid'))."')";
        }
        if ($this->post('createTime')) {
            $timeArr = explode(' ~ ', $this->post('createTime'));
            $and .= " and date(signTime) >='{$timeArr[0]}' and  date(signTime) <='{$timeArr[1]}'";
        }
        //获取查询结果
        $where = $and;
        $page = $this->page(array("table" => self::table,'order'=>" signTime desc",'where'=>$where));
        $_SESSION[self::page]['sql'] = $page['fullSql'];
        //获取表头
        $tr[] = array("姓名", "部门",'打卡记录');
        //获取记录
        foreach ($page['array'] as $array) {
            $job = query('job'," id = '$array[jobId]'");
            $tr[] = array(self::findStaff($array['stid']), departmentClass::getTypeName($job['departmentId']),$array['signTime']);
        }
        //控件
        $button = "";
        if ($this->power(self::page, "import")) {
            $button .=
                importTemplateClass::importTemplateButton(self::page) .
                $this->upButton(array("title" => "导入", "fun" => "importData"));
        }
        if ($this->power(self::page, "export")) {
            $button .= "<a href='" . root . "control/adIndex.html?cla=" . $this->get('cla') . "&fun=exportData'><span class='spanButton'>导出</span></a>\n";
        }
        //返回
        $json['html'] = $page['hint'] .$button . "<form name='listForm'>" . space . $this->tableMany(array("tr" => $tr)) . "</form>" . $page['button'] . clear;
        return json_encode($json, 320);
    }

    /**
     * 下载模板
     */
    public function downloadModel()
    {
        if (!$this->power(self::page, 'import')) {
            $_SESSION['warn'] = '你还没有导入的权限';
        } else {
            $title = ['员工编号', '员工姓名', '打卡时间'];
            $data[] = ['YM202111090001','徐三','2021-11-16 08:00:00'];
            $text = $this->control['name'] . "下载了考勤记录导入模板";
            $this->logText(["type" => self::page, "text" => $text]);
            $this->excelOut($title, $data, '考勤记录导入模板.xlsx');
            exit(0);
        }
        header("Location:" . getenv("HTTP_REFERER"));
    }

    /*数据导入*/
    #[NoReturn] public function importData()
    {
        if (!$this->power(self::page, 'import')) {
            $_SESSION['warn'] = '你还没有导入的权限';
        } else {
            $data = $this->importHead(format: [
                2=>  \Vtiful\Kernel\Excel::TYPE_STRING,
            ]);
            $x = 0;
            $text = '';
            foreach ($data as $i => $row) {
                if ($i > 0) {
                    $cell = formSubArray($row);
                    $staff = query("staff", "orderNo = '$cell[0]'");
                    $field = [
                        "jobId" => $staff['jobId'],//职位
                        "stid" => $staff['stid'],
                        "signTime" => $cell[2],//联系人姓名
                        "type" => '导入',//联系人身份
                    ];
                    $annotation = [
                        'stid' => '员工',
                        'signTime' => '打卡时间',
                    ];
                    $rules = [
                        'stid' => 'required',
                        'signTime' => 'required',
                    ];
                    // 校验是否符合规则
                    $vt = new \verify();
                    $result = $vt->setAliases($annotation)->validate($field, $rules)->isOk();
                    if ($result === false) {
                        $text .= "\n第【{$i}】行，" . $vt->getErrs()[0];
                    } else {
                        //查看范围
                        $warn = insert(["table" => self::table, "field" => $field]);
                        if (in_array($warn, ["新增成功", "更新成功"])) {
                            $x++;
                            $text .= "第【{$i}】行导入成功<br>";
                        } else {
                            $text .= "第【{$i}】行导入失败：编辑失败<br>";
                        }
                    }
                }
            }
            $text = $this->control['name'] . '导入了文件：' . $_FILES['file']['name'] . '：' . $text;
            $this->logText(["type" => self::page, "text" => $text]);
            $warn = "成功导入" . $x . "条记录，详情请查看日志！";
            $_SESSION['warn'] = $warn;
        }
        header("Location:" . getenv("HTTP_REFERER"));
        exit(0);
    }

    /**
     * 导出
     */
    public function exportData()
    {
        if (!$this->power(self::page, 'export')) {
            $_SESSION['warn'] = '你还没有导出的权限';
        } else {
            $data = [];
            $sql = $_SESSION[self::page]['sql'];
            $title = array("姓名", "部门",'打卡记录');
            $result = sqlFind($sql);
            if (is_array($result)) {
                foreach ($result as $array) {
                    $job = query('job'," id = '$array[jobId]'");
                    $data[] = array(self::findStaff($array['stid']), departmentClass::getTypeName($job['departmentId']),$array['signTime']);
                }
            }
            $text = $this->control['name'] . "导出了考勤统计表";
            $this->logText(["type" => self::page, "text" => $text]);
            $this->excelOut($title, $data, "考勤统计表.xlsx");
        }
        header("Location:" . getenv("HTTP_REFERER"));
        exit(0);
    }

    /**
     * 考勤汇总
     *
     */
    public function collect(): bool|string
    {
        $json['html'] = $this->homeForm('考勤汇总');
        return json_encode($json, 320);
    }

    /**
     * 考勤汇总
     * @return bool|string
     */
    public function collectSearch(): bool|string
    {
        //全等查询数组
        $equal = array(
        );
        $like = array(
        );
        $and = " and state <> '离职' ";
        if ($this->post('stid')) {
            $and .= " and stid in ('".implode("','",$this->post('stid'))."')";
        }
        //排序
        $orderBy = $this->orderBy(array(
            "search" => $this->post('orderBy')??null,
            "default" => "月份降序",
            "array" => array(
                "month" => array("desc" => "月份降序", "site" => 5),
            ),
        ));
        $month = $this->post('month')?:date("Y-m");
        $_SESSION[self::page]['month'] = $month;
        $workSignCheckClass = new workSignCheckClass();
        $lastMonthTime = $workSignCheckClass->lastMonthTime($month);//最后一天时间 获取有效薪酬规则
        //休息日
        $holiday = $this->getHoliday(date("Y",strtotime($month.'-01')));
        //获取查询结果
        $where = search(array("=" => $equal, 'like' => $like)) . $and;
        $page = $this->page(array("table" => 'staff', "where" => $where, "order" => 'time asc'));
        $_SESSION[self::page]['collect'] = $page['fullSql'];
        //获取表头
        $tr='';
        //获取记录
        foreach ($page['array'] as $k=>$array) {
            $list = $k+1;
            $department = self::findStaffDepartment($array['stid']);
            //根据员工选择器的值和当前员工的部门，职位，员工ID求交集
            $idColloection = $this->staffCombination(true,$array['stid']);
            //考勤规则
            $clockRule = query('clock_rule', " (concat(',',replace(selector,';',','),',') regexp concat(',',replace('{$idColloection}',',',',|,'),','))>0 and state = '启用' and UNIX_TIMESTAMP(startTime) <=UNIX_TIMESTAMP('{$lastMonthTime}') order by startTime desc");
            //本月工作日
            $workDayResult = $workSignCheckClass->getWorkDay($month, $clockRule['workDayId']);
            $workDay = $workDayResult['workDay']?:[];
            //本月实际考勤天数
            $actualDays         = find([
                    'table'  => "work_sign_single",
                    'field'  => "IFNULL(count(id),0) as num",
                    'single' => " date_format(date, '%Y-%m') = '$month' and stid = '$array[stid]' ",
                ])['num'] ?? 0;
            //正常天数
            $normal         = find([
                    'table'  => "work_sign_single",
                    'field'  => "IFNULL(count(id),0) as num",
                    'single' => " date_format(date, '%Y-%m') = '$month' and stid = '$array[stid]' and sign_start <= start_time and sign_end >= end_time ",
                ])['num'] ?? 0;
            //异常天数
            $abnormal = $actualDays-$normal;
            //工时
            $workingHours       = find([
                    'table'  => "work_sign_single",
                    'field'  => "IFNULL(sum(lawHour),0) as lawHour,IFNULL(sum(workHour),0) as workHour",
                    'single' => " date_format(date, '%Y-%m') = '$month' and stid = '$array[stid]' ",
                ]);
            //异常次数
            $number       = find([
                'table'  => "work_sign_single",
                'field'  => "IFNULL(sum(late),0) as late,IFNULL(sum(early),0) as early,IFNULL(sum(is_leave),0) as is_leave,IFNULL(sum(miss),0) as miss",
                'single' => " date_format(date, '%Y-%m') = '$month' and stid = '$array[stid]' ",
            ]);
            //外出
            $work_out         = find([
                    'table'  => "work_out",
                    'field'  => "IFNULL(sum(timestampdiff(hour,timeStart,timeEnd)),0) as total",
                    'single' => " date_format(timeStart, '%Y-%m') = '$month' and stid = '$array[stid]' and state = '成果确认' ",
                ])['total'] ?? 0;
            //出差
            $bussiness_trip         = find([
                    'table'  => "bussiness_trip",
                    'field'  => "IFNULL(sum(timestampdiff(hour,factStartTime,factEndTime)),0) as total",
                    'single' => " date_format(factStartTime, '%Y-%m') = '$month' and staffId = '$array[stid]' and state = '成果确认' ",
                ])['total'] ?? 0;
            //年假
            $annualLeave         = find([
                    'table'  => "work_holiday",
                    'field'  => "IFNULL(sum(hour),0) as total",
                    'single' => " date_format(startTime, '%Y-%m') = '$month' and stid = '$array[stid]' and state = '已通过' and type = '年假' ",
                ])['total'] ?? 0;
            //事假
            $compassionateLeave         = find([
                    'table'  => "work_holiday",
                    'field'  => "IFNULL(sum(hour),0) as total",
                    'single' => " date_format(startTime, '%Y-%m') = '$month' and stid = '$array[stid]' and state = '已通过' and type = '事假' ",
                ])['total'] ?? 0;
            //病假
            $sickLeave         = find([
                    'table'  => "work_holiday",
                    'field'  => "IFNULL(sum(hour),0) as total",
                    'single' => " date_format(startTime, '%Y-%m') = '$month' and stid = '$array[stid]' and state = '已通过' and type = '病假' ",
                ])['total'] ?? 0;
            //加班小时数，加班开始时间在本月即记为本月加班
            $workAddHour = find(array("table" => "work_add", "sum" => "hour", "where" => " stid = '" . $array['stid'] . "' and date_format(timeStart,'%Y-%m') = '$month' and state = '已通过' "));
            //工作日加班
            $workAddHourWeekday = find(array("table" => "work_add", "sum" => "hour", "where" => " stid = '" . $array['stid'] . "' and date_format(timeStart,'%Y-%m') = '$month' and state = '已通过' and date_format(timeStart,'%Y-%m-%d') in ('".implode("','",$workDay)."') "));
            //休息日加班
            $workAddHourRestDay = find(array("table" => "work_add", "sum" => "hour", "where" => " stid = '" . $array['stid'] . "' and date_format(timeStart,'%Y-%m') = '$month' and state = '已通过' and date_format(timeStart,'%Y-%m-%d') in ('".implode("','",$holiday['holiday'])."') "));
            //节假日加班
            $workAddHourFestival = find(array("table" => "work_add", "sum" => "hour", "where" => " stid = '" . $array['stid'] . "' and date_format(timeStart,'%Y-%m') = '$month' and state = '已通过' and date_format(timeStart,'%Y-%m-%d') in ('".implode("','",$holiday['festival'])."') "));
            //加班调休
            $exceedWorkHour = $workingHours['workHour'] > $workingHours['lawHour']?$workingHours['workHour'] - $workingHours['lawHour']:0;
            $compensatoryLeave = $workAddHour - $exceedWorkHour; //总加班时长-超出标准的工时=加班调休时长
            $tr .= <<<eof
                 <tr>
                    <td>{$list}</td>
                    <td>{$month}</td>
                    <td>{$array['name']}</td>
                    <td>{$department}</td>
                    <td>{$clockRule['team']}</td>
                    <td>{$workDayResult['dayNum']}</td><td>{$normal}</td><td>{$abnormal}</td><td>{$workingHours['lawHour']}</td><td>{$workingHours['workHour']}</td>
                    <td>{$number['late']}</td><td>{$number['early']}</td><td>{$number['is_leave']}</td><td>{$number['miss']}</td>
                    <td>{$work_out}</td><td>{$bussiness_trip}</td><td>{$annualLeave}</td><td>{$compassionateLeave}</td><td>{$sickLeave}</td>
                    <td>{$workAddHourWeekday}</td><td>{$workAddHourRestDay}</td><td>{$workAddHourFestival}</td><td>{$compensatoryLeave}</td>
                </tr>
eof;
        }

        $html = <<<eof
<table id="subjectTable" class="tableMany">
    <thead>
        <tr>
            <td rowspan="2" style="vertical-align:middle">序号</td>
            <td rowspan="2" style="vertical-align:middle">月份</td>
            <td rowspan="2" style="vertical-align:middle">姓名</td>
            <td rowspan="2" style="vertical-align:middle">部门</td>
            <td rowspan="2" style="vertical-align:middle">考勤组</td>
            <td colspan="5" class="center">概况</td>
            <td colspan="4" class="center">异常</td>
            <td colspan="5" class="center">假勤</td>
            <td colspan="4" class="center">加班</td>
        </tr>
        <tr>
            <td>工作天数</td><td>正常天数</td><td>异常天数</td><td>标准工时</td><td>实际工时</td>
            <td>迟到次数</td><td>早退次数</td><td>旷工次数</td><td>缺卡次数</td>
            <td>外出</td><td>出差</td><td>年假</td><td>事假</td><td>病假</td>
            <td>工作日加班</td><td>休息日加班</td><td>节假日加班</td><td>加班调休</td>
        </tr>
    </thead>
    <tbody>
     {$tr}
    </tbody>
</table>
<style>
#subjectTable td{white-space:nowrap}
#subjectTable{margin-left: 0;}
.listForm{overflow: auto;width: calc(100%);padding: 10px 0}
</style>
eof;



        //控件
        $button = "";
        //导出
        if ($this->power(self::page, "export")) {
            $button .= "  <a href='" . root . "control/adIndex.html?cla=" . $this->get('cla') . "&fun=exportDataCollect'><span class='spanButton'>导出</span></a>\n";
        }
        //返回
        $json['html'] = $page['hint'] . $button . "<form class='listForm' name='listForm'>" . space . $html . "</form>" . $page['button'];

        $json['html'] .= <<<eof
<script>
         
</script>
eof;

        return json_encode($json, 320);
    }
    /**
     * 获取当年的节假日
     */
    public function getHoliday($year): array
    {
        $holiday = query('holiday', " year = '$year'");
        $holidayArr = $holiday['workDay'] ? json_decode($holiday['workDay'], true) : [];//休息日数组
        $festivalArr = $holiday['festival'] ? json_decode($holiday['festival'], true) : [];//国定假数组
        return array('holiday' => $holidayArr, 'festival' => array_keys($festivalArr));
    }

    /**
     * 导出
     */
    public function exportDataCollect()
    {
        if (!$this->power(self::page, 'export')) {
            $_SESSION['warn'] = '你还没有导出的权限';
        } else {
            $data = [];
            $sql = $_SESSION[self::page]['collect'];
            $title = array("序号", "月份",'姓名','部门','考勤组','工作天数','正常天数','异常天数','标准工时','实际工时','迟到次数','早退次数','旷工次数','缺卡次数','外出','出差','年假','事假','病假','工作日加班','休息日加班','节假日加班','加班调休');
            $result = sqlFind($sql);
            if (is_array($result)) {
                $month = $_SESSION[self::page]['month'];
                $workSignCheckClass = new workSignCheckClass();
                $lastMonthTime = $workSignCheckClass->lastMonthTime($month);//最后一天时间 获取有效薪酬规则
                //休息日
                $holiday = $this->getHoliday(date("Y",strtotime($month.'-01')));
                foreach ($result as $k=>$array) {
                    $list = $k+1;
                    $department = self::findStaffDepartment($array['stid']);
                    //根据员工选择器的值和当前员工的部门，职位，员工ID求交集
                    $idColloection = $this->staffCombination(true,$array['stid']);
                    //考勤规则
                    $clockRule = query('clock_rule', " (concat(',',replace(selector,';',','),',') regexp concat(',',replace('{$idColloection}',',',',|,'),','))>0 and state = '启用' and UNIX_TIMESTAMP(startTime) <=UNIX_TIMESTAMP('{$lastMonthTime}') order by startTime desc");
                    //本月工作日
                    $workDayResult = $workSignCheckClass->getWorkDay($month, $clockRule['workDayId']);
                    $workDay = $workDayResult['workDay']?:[];
                    //本月实际考勤天数
                    $actualDays         = find([
                            'table'  => "work_sign_single",
                            'field'  => "IFNULL(count(id),0) as num",
                            'single' => " date_format(date, '%Y-%m') = '$month' and stid = '$array[stid]' ",
                        ])['num'] ?? 0;
                    //正常天数
                    $normal         = find([
                            'table'  => "work_sign_single",
                            'field'  => "IFNULL(count(id),0) as num",
                            'single' => " date_format(date, '%Y-%m') = '$month' and stid = '$array[stid]' and sign_start <= start_time and sign_end >= end_time ",
                        ])['num'] ?? 0;
                    //异常天数
                    $abnormal = $actualDays-$normal;
                    //工时
                    $workingHours       = find([
                        'table'  => "work_sign_single",
                        'field'  => "IFNULL(sum(lawHour),0) as lawHour,IFNULL(sum(workHour),0) as workHour",
                        'single' => " date_format(date, '%Y-%m') = '$month' and stid = '$array[stid]' ",
                    ]);
                    //异常次数
                    $number       = find([
                        'table'  => "work_sign_single",
                        'field'  => "IFNULL(sum(late),0) as late,IFNULL(sum(early),0) as early,IFNULL(sum(is_leave),0) as is_leave,IFNULL(sum(miss),0) as miss",
                        'single' => " date_format(date, '%Y-%m') = '$month' and stid = '$array[stid]' ",
                    ]);
                    //外出
                    $work_out         = find([
                            'table'  => "work_out",
                            'field'  => "IFNULL(sum(timestampdiff(hour,timeStart,timeEnd)),0) as total",
                            'single' => " date_format(timeStart, '%Y-%m') = '$month' and stid = '$array[stid]' and state = '成果确认' ",
                        ])['total'] ?? 0;
                    //出差
                    $bussiness_trip         = find([
                            'table'  => "bussiness_trip",
                            'field'  => "IFNULL(sum(timestampdiff(hour,factStartTime,factEndTime)),0) as total",
                            'single' => " date_format(factStartTime, '%Y-%m') = '$month' and staffId = '$array[stid]' and state = '成果确认' ",
                        ])['total'] ?? 0;
                    //年假
                    $annualLeave         = find([
                            'table'  => "work_holiday",
                            'field'  => "IFNULL(sum(hour),0) as total",
                            'single' => " date_format(startTime, '%Y-%m') = '$month' and stid = '$array[stid]' and state = '已通过' and type = '年假' ",
                        ])['total'] ?? 0;
                    //事假
                    $compassionateLeave         = find([
                            'table'  => "work_holiday",
                            'field'  => "IFNULL(sum(hour),0) as total",
                            'single' => " date_format(startTime, '%Y-%m') = '$month' and stid = '$array[stid]' and state = '已通过' and type = '事假' ",
                        ])['total'] ?? 0;
                    //病假
                    $sickLeave         = find([
                            'table'  => "work_holiday",
                            'field'  => "IFNULL(sum(hour),0) as total",
                            'single' => " date_format(startTime, '%Y-%m') = '$month' and stid = '$array[stid]' and state = '已通过' and type = '病假' ",
                        ])['total'] ?? 0;
                    //加班小时数，加班开始时间在本月即记为本月加班
                    $workAddHour = find(array("table" => "work_add", "sum" => "hour", "where" => " stid = '" . $array['stid'] . "' and date_format(timeStart,'%Y-%m') = '$month' and state = '已通过' "));
                    //工作日加班
                    $workAddHourWeekday = find(array("table" => "work_add", "sum" => "hour", "where" => " stid = '" . $array['stid'] . "' and date_format(timeStart,'%Y-%m') = '$month' and state = '已通过' and date_format(timeStart,'%Y-%m-%d') in ('".implode("','",$workDay)."') "));
                    //休息日加班
                    $workAddHourRestDay = find(array("table" => "work_add", "sum" => "hour", "where" => " stid = '" . $array['stid'] . "' and date_format(timeStart,'%Y-%m') = '$month' and state = '已通过' and date_format(timeStart,'%Y-%m-%d') in ('".implode("','",$holiday['holiday'])."') "));
                    //节假日加班
                    $workAddHourFestival = find(array("table" => "work_add", "sum" => "hour", "where" => " stid = '" . $array['stid'] . "' and date_format(timeStart,'%Y-%m') = '$month' and state = '已通过' and date_format(timeStart,'%Y-%m-%d') in ('".implode("','",$holiday['festival'])."') "));
                    //加班调休
                    $exceedWorkHour = $workingHours['workHour'] > $workingHours['lawHour']?$workingHours['workHour'] - $workingHours['lawHour']:0;
                    $compensatoryLeave = $workAddHour - $exceedWorkHour; //总加班时长-超出标准的工时=加班调休时长
                    $data[] = array($list, $month,$array['name'],$department,$clockRule['team'],$workDayResult['dayNum'],$normal,$abnormal,$workingHours['lawHour'],$workingHours['workHour'],$number['late'],$number['early'],$number['is_leave'],$number['miss'],$work_out,$bussiness_trip,$annualLeave,$compassionateLeave,$sickLeave,$workAddHourWeekday,$workAddHourRestDay,$workAddHourFestival,$compensatoryLeave);
                }
            }
            $text = $this->control['name'] . "导出了考勤汇总表";
            $this->logText(["type" => self::page, "text" => $text]);
            $this->excelOut($title, $data, "考勤汇总表.xlsx");
        }
        header("Location:" . getenv("HTTP_REFERER"));
        exit(0);
    }

}

